package dao;


import model.User;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class UserDao {
    public boolean insert(User user) throws SQLException{
        Statement stmt = null;
        ResultSet rs=null;
        Connection conn=null;
        try {
            conn= JDBCUtils.getConnection();
            stmt=conn.createStatement();
            String sql="insert into users ( name, password)" +
                    "values ('"
                    +user.getName()
                    +"','"
                    +user.getPassword()
                    +"');";
            int num=stmt.executeUpdate(sql);
            if(num>0)
                return true;
            else
                return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }
        return false;
    }
    public ArrayList<User> findAll() throws SQLException{
        Connection conn=null;
        Statement stmt = null;
        ResultSet rs=null;
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn = JDBCUtils.getConnection();
            stmt=conn.createStatement();
            String sql="select * from users";
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                User user= new User(rs.getInt("id")
                        ,rs.getString("name")
                        ,rs.getString("password"));
                list.add(user);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(rs,stmt,conn);
        }
        return null;
    }
    public User findUser(int id) throws SQLException{
        Statement stmt = null;
        ResultSet rs=null;
        Connection conn=null;
        try {
            conn=JDBCUtils.getConnection();
            stmt=conn.createStatement();
            String sql="select * from users where id="+id;
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                User user= new User(rs.getInt("id")
                        ,rs.getString("name")
                        ,rs.getString("password"));
                return user;
            }
            return null;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }
        return null;
    }
    public boolean delete(int id) throws SQLException{
        Statement stmt = null;
        ResultSet rs=null;
        Connection conn=null;
        try {
            conn=JDBCUtils.getConnection();
            stmt=conn.createStatement();
            String sql="delete from users where id="+id;
            int num= stmt.executeUpdate(sql);
            if(num>0)
                return true;
            return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }
        return false;
    }
    public boolean update(User user) throws SQLException{
        Statement stmt = null;
        ResultSet rs=null;
        Connection conn=null;
        try {
            conn = JDBCUtils.getConnection();
            stmt=conn.createStatement();
            String sql="updata users set name='"+user.getName()
                    +"',password='"+user.getPassword()
                    +"' where id="+user.getId();
            int num=stmt.executeUpdate(sql);
            if(num>0)
                return true;
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }
        return false;
    }
    public boolean isUsernameExist(String name) throws SQLException{
        Statement stmt=null;
        Connection conn=null;
        ResultSet rs=null;

        try {
            conn=JDBCUtils.getConnection();
            stmt=conn.createStatement();
            String sql="select name from users where name='"+name+"';";
            rs=stmt.executeQuery(sql);
            int num=0;
            while (rs.next()){
                num++;
            }
            if(num>0)
                return true;
            else
                return false;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }
        return false;
    }
    public boolean isUserNamePasswordExist(String name,String password) throws SQLException{
        Statement stmt=null;
        Connection conn=null;
        ResultSet rs=null;

        try {
            conn=JDBCUtils.getConnection();
            stmt=conn.createStatement();
            String sql = "select * from users where name='"+name+"' and password='"+password+"';";
            rs=stmt.executeQuery(sql);
            int num=0;
            while (rs.next()){
                num++;
            }
            if(num>0)
                return true;
            else
                return false;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,stmt,conn);
        }

        return false;
    }
}
